package com.z.dao.impl;

import com.z.dao.UsersDao;
import com.z.pojo.Goods;
import com.z.pojo.Users;
import com.z.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 张福兴
 * date 2021/9/1
 */


public class UsersDaoImpl implements UsersDao {
    private Connection conn = null;
    private PreparedStatement ps = null;

    @Override
    public Users selectOneUsers(String account, String password, Integer level) throws SQLException {
        conn = DBUtil.getConnection();
        @SuppressWarnings("SqlResolve")
        String sql="select  * from users where account=? and password=? and level=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1,account);
        ps.setString(2,password);
        ps.setInt(3,level);
        ResultSet user = ps.executeQuery();
        Users users = null;
        if (user.next()) {
            users = new Users();
            users.setUid(user.getInt("uid"));
            users.setUname(user.getString("uname"));
            users.setAccount(user.getString("account"));
            users.setPassword(user.getString("password"));
            users.setLevel(user.getInt("level"));
            users.setGender(user.getString("gender"));
            users.setPhone(user.getString("phone"));
        }
        DBUtil.close(ps,conn);
        return users;
    }

    @Override
    public List<Users> selectPageUsers(Integer nowpage, Integer eachcount, String term) throws SQLException {
        this.conn = DBUtil.getConnection();
        String sql = "";
        if (term == null || "".equals(term)) {
            sql = "select * from users limit ?,?;";

        } else {
            sql = "select * from users where "+term+" limit ?,?;";
        }
        ps = conn.prepareStatement(sql);

        ps.setInt(1,(nowpage-1)*eachcount);
        ps.setInt(2,eachcount);


        ResultSet resultSet = ps.executeQuery();
        List<Users> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new Users(
                    resultSet.getInt("uid"),
                    resultSet.getString("uname"),
                    resultSet.getString("account"),
                    resultSet.getString("password"),
                    resultSet.getInt("level"),
                    resultSet.getString("gender"),
                    resultSet.getString("phone")
            ));
        }
        DBUtil.close(this.ps,this.conn);
        return list;
    }

    @Override
    public Integer count(String term) throws SQLException {
        this.conn = DBUtil.getConnection();
        String sql="";
        if(term==null||"".equals(term)){
            sql="select count(*) as 'count' from users;";

        }else {
            sql="select count(*) as 'count' from users  where "+ term+";" ;
        }
        this.ps =conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        Integer count = 0;
        if(resultSet.next()){
            count = resultSet.getInt("count");
        }
        DBUtil.close(ps,conn);
        return count;
    }

    @Override
    public Integer updatebyuid(Integer  uid,Integer level ) throws SQLException {
        this.conn = DBUtil.getConnection();
        String sql="update  users set level = ? where uid = ?";
        this.ps =conn.prepareStatement(sql);
        this.ps.setInt(1,level);
        this.ps.setInt(2,uid);
        int i = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return i;
    }

    @Override
    public Users selectUsersByuid(Integer uid) throws SQLException {
        conn=DBUtil.getConnection();
        String sql="select * from users where uid=?";
        ps=conn.prepareStatement(sql);
        ps.setInt(1,uid);
        ResultSet resultSet = ps.executeQuery();
        Users users = null;
        if (resultSet.next()){
            users = new Users();
            users.setUid(resultSet.getInt("uid"));
            users.setAccount(resultSet.getString("account"));
            users.setGender(resultSet.getString("gender"));
            users.setLevel(resultSet.getInt("level"));
            users.setPassword(resultSet.getString("password"));
            users.setPhone(resultSet.getString("phone"));
            users.setUname(resultSet.getString("uname"));
        }
        DBUtil.close(ps,conn);
        return users;
    }

    @Override
    public Integer updateUsersByuid(Users users) throws SQLException {
        conn=DBUtil.getConnection();
        String sql="update  users set level = ?,account=?,gender=?,password=?,phone=?,uname=? where uid = ?";
        ps=conn.prepareStatement(sql);
        ps.setInt(1,users.getLevel());
        ps.setString(2,users.getAccount());
        ps.setString(3,users.getGender());
        ps.setString(4,users.getPassword());
        ps.setString(5,users.getPhone());
        ps.setString(6,users.getUname());
        ps.setInt(7,users.getUid());
        int i = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return i;
    }

    @Override
    public Integer insertUsers(Users users) throws SQLException {
        conn=DBUtil.getConnection();

        String sql="insert into users(`uname`,`account`,`password`,`level`,`gender`,`phone`) value (?,?,?,?,?,?)";
        ps=conn.prepareStatement(sql);
        ps.setString(1,users.getUname());
        ps.setString(2,users.getAccount());
        ps.setString(3,users.getPassword());
        ps.setInt(4,users.getLevel());
        ps.setString(5,users.getGender());
        ps.setString(6,users.getPhone());
        int i = ps.executeUpdate();
        DBUtil.close(ps,conn);
        return i;
    }

}
